<?php
namespace app\data\service;
use think\admin\Library;
use app\data\model\DataBreedWeightRanges;
use app\data\model\DataCatLog;
use app\data\model\DataCatCat;
use think\facade\Db;
class CatService
{
    /**
     * 检查宠物体重是否标准
     * @param array $data
     * @return array
     */
    function check_cat_weight($data)
    {
        //$data=['breed_id'=>'品种ID','month'=>'月份','gender'=>'性别','now_weight'=>'当前体重'];
          $res = DataBreedWeightRanges::mk()
            ->where(['breed_id'=>$data['breed_id'],'gender'=>$data['gender']])
            ->where('month','<=',$data['month'])->order('month desc')
            ->field('min_weight','max_weight')->find();
          if(empty($res)){
              $res['state']='error';
              $res['data'] = [];
              $res['message']='未获取到参考数据';
          }elseif($res['min_weight']>$data['now_weight']){
              //偏瘦
              $res['state']=-1;
              $res['data']=$res['min_weight'] - $data['now_weight'];
          }elseif($res['max_weight'] < $data['now_weight']){
              //偏胖
              $res['state']=1;
              $res['data']=$res['now_weight'] - $data['max_weight'];
          }else{
              //正常
              $res['state']=0;
              $res['data']=[];
          }
          return $res;
    }
    /**
     * 检查宠物昨日排便次数
     * @param array $data
     * @return array
     */
    function check_cat_yesterday_wc_times ($data)
    {
        //$data=['breed_id'=>'品种ID','month'=>'月份','gender'=>'性别','yesterday_times'=>'今日排便次数'];
        $times = DataBreedWeightRanges::mk()
            ->where(['breed_id'=>$data['breed_id'],'gender'=>$data['gender']])
            ->where('month','<=',$data['month'])->order('month desc')
            ->value('toilet_times');
        if($times == ''){
            $res['state']='error';
            $res['data'] = [];
            $res['message']='未获取到参考数据';
        }elseif($times > $data['yesterday_times']){
            //次数偏少
            $res['state']=-1;
            $res['data']=$times - $data['yesterday_times'];
        }elseif($times < $data['yesterday_times']){
            //次数偏多
            $res['state']=1;
            $res['data']=$res['yesterday_times'] - $times;
        }else{
            //正常
            $res['state']=0;
            $res['data']=[];
        }
        return $res;
    }

    function toilet_chart_data_by_mid($member_id,$cat_id,$type,$date=''){
        $where=[];
        if($cat_id!= ''){
            $where[] = ['l.cat_id','=',$cat_id];
        }
        //today,week,month
        if($type == 0 || $type == 3){
            $cats_list = DataCatLog::mk()
                ->alias('l') ->join('data_cat c','c.id = l.cat_id')
                ->join('data_device d','d.iotId = l.iot_id')
                ->where('c.member_id', $member_id)
                ->where($where)->field('c.id as id,CONCAT(d.nickname,"-",c.nickname) as name')->group('cat_id')
                ->filter(function($item) use($type,$date){
                    $item['data'] = $this->getData($item['id'],$type,$date);
                    return $item;
                })
                ->order('l.id asc')->select()->toArray();
            foreach($cats_list as $k =>$v){
                if(empty($v['data'])){
                    unset($cats_list[$k]);
                }
            }
            return array_values($cats_list);
        }else{
            //7天或者30天
            $days = $type == '1'?7:90;
            $days_array = getLastSevenDays($days); //获取近期日期数量
            $startTime = date('Y-m-d H:i:s', strtotime('-'.$days.' days')); // $days天前的开始时间
            $endTime = date('Y-m-d H:i:s'); // 当前时间
            $cats = DataCatLog::mk()->alias('l')->join('data_cat c','c.id = l.cat_id')
                ->join('data_device d','d.iotId = l.iot_id')
                ->where($where)
                ->where('c.member_id',$member_id)
                ->where('in_time', '>=', $startTime)
                ->where('in_time', '<=', $endTime)
                ->where('cat_id', '>', 0)
                ->group('cat_id')
                ->column('cat_id,c.nickname as c_nickname,d.nickname as d_nickname');
            $toilet=[];
            foreach($cats as $k =>$v){
                $toilet[$k]['name']=$v['d_nickname'].'-'.$v['c_nickname'];
                $toilet[$k]['data']=$this->getTotalDataToilet($v['cat_id'],$days_array);
            }
            $res['categories'] =  formatDate($days_array);
            $res['series'] = $toilet;
            return $res;
        }

    }
    function weight_chart_data_by_mid($member_id,$cat_id,$type,$date='')
    {
        $where = [];
        if ($cat_id != '') {
            $where[] = ['l.cat_id', '=', $cat_id];
        }
        if ($type == '0' || $type == 3) {
            $cats_list = DataCatLog::mk()
                ->alias('l')->join('data_cat c', 'c.id = l.cat_id')
                ->join('data_device d','d.iotId = l.iot_id')
                ->where('c.member_id', $member_id)
                ->where($where)->field('c.id as id,CONCAT(d.nickname,"-",c.nickname) as name')->group('cat_id')
                ->filter(function ($item) use ($type, $date) {
                    $item['data'] = $this->getDataWeight($item['id'], $type, $date);
                    return $item;
                })
                ->order('l.id asc')->select()->toArray();
            foreach ($cats_list as $k => $v) {
                if (empty($v['data'])) {
                    unset($cats_list[$k]);
                }
            }
            return array_values($cats_list);
        } else {
            $days = $type == '1' ? 7 : 90;
            $days_array = getLastSevenDays($days); //获取近期日期数量
            $startTime = date('Y-m-d H:i:s', strtotime('-' . $days . ' days')); // $days天前的开始时间
            $endTime = date('Y-m-d H:i:s'); // 当前时间
            $cats = DataCatLog::mk()->alias('l')->join('data_cat c', 'c.id = l.cat_id')
                ->join('data_device d','d.iotId = l.iot_id')
                ->where('c.member_id', $member_id)
                ->where($where)
                ->where('in_time', '>=', $startTime)
                ->where('in_time', '<=', $endTime)
                ->where('cat_id', '>', 0)
                ->group('cat_id')
                ->column('cat_id,c.nickname as c_nickname,d.nickname as d_nickname');
            $toilet = [];
            foreach ($cats as $k => $v) {
                $toilet[$k]['name'] = $v['d_nickname'].'-'.$v['c_nickname'];
                $toilet[$k]['data'] = $this->getTotalDataWeight($v['cat_id'], $days_array);
            }
            $res['categories'] = formatDate($days_array);
            $res['series'] = $toilet;
            return $res;
        }
    }
    function weight_chart_data_by_mid_new($member_id,$cat_id,$type,$date=''){
        $map[]=['member_id','=',$member_id];
        if($cat_id!= ''){
            $map[] = ['l.cat_id','=',$cat_id];
        }
        $cats_list = DataCatLog::mk()
            ->alias('l') ->join('data_cat c','c.id = l.cat_id')
            ->where($map)->field('c.id as id,nickname as name')->group('cat_id')
            ->filter(function($item) use($type,$date){
                $item['data'] = $this->getDataWeight($item['id'],$type,$date);
                return $item;
            })
            ->order('l.id asc')->select()->toArray();
        foreach($cats_list as $k =>$v){
            if(empty($v['data'])){
                unset($cats_list[$k]);
            }
        }
        return array_values($cats_list);
    }

    //格式化如厕图表数据
    protected function getData($id,$type,$date){
        if($type == 0 || $type == 3){
            $toilet = DataCatLog::mk()
                ->whereDay('in_time',$type==0?'today':$date)->where(['cat_id'=>$id])
                ->field("in_time as time,duration as value")->select()->toArray();
        }else{
            $days = $type == '1'?7:90;
            $start_time =   date("Y-m-d",strtotime("-{$days} day"))." 00:00:00";
            $end_time =   date("Y-m-d H:i:s");
            $toilet = DataCatLog::mk()->where(['cat_id'=>$id])
                ->group('date_format(in_time,"%Y-%m-%d")')
                ->whereTime('in_time', '<=', $end_time)
                ->whereTime('in_time', '>=', $start_time)
                ->field("in_time as time,count(*) as value")->select()->toArray();
        }
        $res = [];
        if(count($toilet)>0){
            foreach($toilet as $v){
                array_push($res,[strtotime($v['time']),$v['value']]);
            }
        }
        return $res;
    }
    protected function getDataWeight($id,$type,$date){
        if($type == 0 || $type == 3){
            $toilet = DataCatLog::mk()
                ->whereDay('in_time',$type==0?'today':$date)->where(['cat_id'=>$id])
                ->field("FORMAT(weight/1000,2) as value,in_time as time,cat_id as id")->select()->toArray();
        }else{
            $days = $type == '1'?7:30;
            $start_time =   date("Y-m-d",strtotime("-{$days} day"))." 00:00:00";
            $end_time =   date("Y-m-d H:i:s");
            $field = 'FORMAT(l.weight/1000,2) as value,date_format(l.in_time,"%Y-%m-%d") as time,cat_id as id';
            $subQuery  = DataCatLog::mk()->alias('l')->field($field)
                ->join('data_cat c','c.id = l.cat_id')
                ->join('data_device d','d.iotId = l.iot_id')
                ->where(['cat_id'=>$id])
                ->whereTime('l.in_time', '<=', $end_time)
                ->whereTime('l.in_time', '>=', $start_time)->order('l.in_time desc')
                ->buildSql();
            $toilet =Db::table($subQuery . ' a')
                ->field('value,time,id')
                ->group('time')
                ->select()->toArray();
        }
        $res = [];
        if(count($toilet)>0){
            foreach($toilet as $v){
                array_push($res,[strtotime($v['time']),$v['value']]);
            }
        }
        return $res;
    }
    protected function getTotalDataToilet($cat_id,$days_array){
        $res=[];

        foreach($days_array as  $v){
            $count =  DataCatLog::mk()->where(['cat_id'=>$cat_id])->whereDay('in_time',$v)->count();
            array_push($res,$count);
        }
        return $res;
    }
    protected function getTotalDataWeight($cat_id,$days_array){
        $res=[];

        foreach($days_array as  $v){
            $weight =  DataCatLog::mk()->where(['cat_id'=>$cat_id])->whereDay('in_time',$v)->value('weight');
            $count = $weight?$weight/1000:'';
            array_push($res,$count);
        }
        return $res;
    }
    //上传宠物 排序，头像，昵称给设备
    public function upload_pet_to_device($member_id,$cat_id){
        $cats = DataCat::mk()->where(['member_id'=>$member_id,'id'=>$cat_id])->field('id,nickname,avatar_thumb')->find();
    }

}
